﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Objects;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data;
using MINERP.DAL;
using MINERP.COM;
using System.Data.EntityClient;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using MINERP.DAL.CommonDAL;

namespace MINERP.DAL.LeaderOperate
{
    public class LeaderTaskDAL : DALBase
    {
        //string conStr = ConfigurationManager.ConnectionStrings["fffff"].ToString();
        string conStr = DALBase.ConnStr;
        //private Entities entity =new Entities();
        /// <summary>
        /// 根据状态和可操作字串取得任务
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOperate"></param>
        /// <returns></returns>       
        public List<WorkOrderFlowList> GetTaskByStatusAndOpe(int inStatus, string inOperate)
        {



            string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus + " AND it.tb_Tech.TechId IN  " + inOperate;
            //DataEntites.Refresh();
            //ObjectQuery<WorkOrderFlowList> flowLists = DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL).Include("orderInfo").Include("tb_Tech");
            ObjectQuery<WorkOrderFlowList> flowLists = DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL);
            //List<WorkOrderFlowList> rtnList = new List<WorkOrderFlowList>();
            //foreach (var list in flowLists)
            //{
            //    rtnList.Add(list);
            //}
            //return rtnList;
            return flowLists.ToList();
        }
        /// <summary>
        /// 根据状态和可操作字串取得任务(刷新)
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOperate"></param>
        /// <returns></returns>       
        public List<WorkOrderFlowList> GetTaskByStatusAndOpe1(int inStatus, string inOperate)
        {
            int statusDoing = (int)OrderInfoStatus.Doing;
            string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus + "and it.orderInfo.status=" + statusDoing + " AND it.tb_Tech.TechId IN  " + inOperate;
            //DataEntites.Refresh();
            using (newSampleYPDBEntities ds = new newSampleYPDBEntities())
            {
                ObjectQuery<WorkOrderFlowList> flowLists = ds.CreateQuery<WorkOrderFlowList>(TaskSQL).Include("orderInfo").Include("tb_Tech");
                List<WorkOrderFlowList> rtnList = new List<WorkOrderFlowList>();
                foreach (var list in flowLists)
                {
                    rtnList.Add(list);
                }
                return rtnList;
            }
        }
        //private Entities entity =new Entities();
        /// <summary>
        /// 根据状态和可操作字串取得任务
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOperate"></param>
        /// <returns></returns>       
        public List<WorkOrderFlowList> GetTaskByOrderIDAndOpe(string OrderID, string inOperate)
        {
            string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.orderInfo.OrderProductId in" + OrderID + " AND it.tb_Tech.TechId IN " + inOperate;
            //DataEntites.Refresh();
            //ObjectQuery<WorkOrderFlowList> flowLists = DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL).Include("orderInfo").Include("tb_Tech");
            //List<WorkOrderFlowList> rtnList = new List<WorkOrderFlowList>();
            //foreach (var list in flowLists)
            //{
            //    rtnList.Add(list);
            //}
            //return rtnList;
            return DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL).ToList();


        }
        /// <summary>
        /// 根据工号查找员工信息
        /// </summary>
        /// <param name="inUserId"></param>
        /// <returns></returns>
        public users GetUserById(int inUserId)
        {
            string userSql = " it.userID={0}";
            userSql = string.Format(userSql, inUserId);
            ObjectQuery<users> userses = new newSampleYPDBEntities().users.Where(userSql).Include("tb_WorkerGroup");
            if (userses.Count() > 0)
            {
                return userses.First();
            }
            else
            {
                return new users();
            }

            //return new newSampleYPDBEntities().users.FirstOrDefault(u => u.userID == inUserId);
        }

        /// <summary>
        /// 根据Id取得定单包
        /// </summary>
        /// <param name="inOrderBagId"></param>
        /// <returns></returns>
        public Tb_OrderBag GetOrderBagById(int inOrderBagId)
        {
            string bagSQL = " it.OrderBagId ={0}";
            bagSQL = string.Format(bagSQL, inOrderBagId);
            ObjectQuery<Tb_OrderBag> bags = DataEntites.Tb_OrderBag.Where(bagSQL).Include("Shop");
            if (bags.Count() > 0)
            {
                return bags.First();
            }
            else
            {
                return new Tb_OrderBag();
            }
        }
        //public int GetPreDateTime(WorkOrderFlowList workOrderFlow)
        //{

        //}

        /// <summary>
        /// 根据Id取得产品
        /// </summary>
        /// <param name="inOrderProductId"></param>
        /// <returns></returns>
        public orderInfo GetorderInfoById(int inOrderProductId)
        {
            string productSQL = " it.OrderProductId = {0}";
            productSQL = string.Format(productSQL, inOrderProductId);
            ObjectQuery<orderInfo> infos = DataEntites.orderInfo.Where(productSQL).Include("Tb_OrderBag");
            DataEntites.Refresh(RefreshMode.StoreWins, infos);
            if (infos.Count() > 0)
            {
                return infos.First();
            }
            else
            {
                return new orderInfo();
            }
        }
        /// <summary>
        /// 组长获得组员信息
        /// </summary>
        /// <param name="inGroupId"></param>
        /// <returns></returns>
        public List<users> GetUsersByLeaderId(int inGroupId)
        {
            //string userSql = "it.tb_WorkerGroup.GroupId={0} and it.IsLeader=false";
            string userSql = "it.tb_WorkerGroup.GroupId={0} and it.IsUseful=true";//phj 3-24日修改
            userSql = string.Format(userSql, inGroupId);
            //DataEntites.Refresh(RefreshMode.StoreWins, users);
            ObjectQuery<users> userses = DataEntites.users.Where(userSql);
            DataEntites.Refresh(RefreshMode.StoreWins, userses);
            List<users> userList = new List<users>();
            foreach (var userse in userses)
            {
                userList.Add(userse);
            }
            return userList;
        }
        /// <summary>
        /// 组长获得在线组员信息
        /// </summary>
        /// <param name="inGroupId"></param>
        /// <returns></returns>
        public List<users> GetOnlineUsersByLeaderId(int inGroupId)
        {
            string userSql = "it.tb_WorkerGroup.GroupId={0} and it.IsLeader=false and it.IsOnline=true";
            userSql = string.Format(userSql, inGroupId);
            ObjectQuery<users> userses = DataEntites.users.Where(userSql);
            List<users> userList = new List<users>();
            foreach (var userse in userses)
            {
                userList.Add(userse);
            }
            return userList;
        }
        /// <summary>
        /// 获取组员任务
        /// </summary>
        /// <param name="inUserID"></param>
        /// <param name="inStatus"></param>
        /// <returns></returns>
        public List<WorkOrderFlowList> GetUserCurrTask(int inUserID, int inStatus)
        {
            string taskSql = "it.UserId={0} and it.Status={1}";
            taskSql = string.Format(taskSql, inUserID, inStatus);
            ObjectQuery<WorkOrderFlowList> flowLists = DataEntites.WorkOrderFlowList.Where(taskSql);
            DataEntites.Refresh(RefreshMode.StoreWins, flowLists);
            List<WorkOrderFlowList> rtnFlow = new List<WorkOrderFlowList>();
            foreach (var list in flowLists)
            {
                rtnFlow.Add(list);
            }
            return rtnFlow;
        }
        /// <summary>
        /// 获取组员任务的照片总量                  温州修改
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="UseableStatus"></param>
        /// <param name="acceptStatus"></param>
        /// <returns></returns>
        public int GetUserPhotoNum(int userId, int UseableStatus, int acceptStatus)
        {
            string esql = "select value count(it.photoId) from photoInfo as it where it.orderInfo.OrderProductId in (select value distinct(itt.orderInfo.OrderProductId) from WorkOrderFlowList as itt where itt.UserId=" + userId + " and (itt.Status=" + UseableStatus + " or itt.Status=" + acceptStatus + "))";
            int query = DataEntites.CreateQuery<int>(esql).FirstOrDefault();
            return query;

        }
        /// <summary>
        /// 获取组员任务的照片总量                  
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="UseableStatus"></param>
        /// <param name="acceptStatus"></param>
        /// <returns></returns>
        public int GetUserPhotoNum(int userId, int Status)
        {
            string esql = "select value count(it.photoId) from photoInfo as it where it.orderInfo.OrderProductId in (select value distinct(itt.orderInfo.OrderProductId) from WorkOrderFlowList as itt where itt.UserId=" + userId + " and (itt.Status=" + Status + "))";
            int query = DataEntites.CreateQuery<int>(esql).FirstOrDefault();
            return query;

        }

        /// <summary>
        /// 取到定单照片数量
        /// 
        /// </summary>
        /// <param name="inProductId"></param>
        /// <returns></returns>
        public int GetPhotoNumByProductId(int inProductId)
        {
            string photoSql = "select value it from photoInfo as it where it.orderInfo.OrderProductId =" + inProductId;
            int Count = DataEntites.CreateQuery<int>(photoSql).Count();
            return Count;
        }
        /// <summary>
        /// 取到定单照片数量
        /// </summary>
        /// <param name="inProductId"></param>
        /// <returns></returns>
        public int GetPhotoNumByFlowIDs(string inFlowsIDs)
        {
            Database db = null;
            int num = 0;
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string strSQL = " select count(*) as Num from dbo.WorkOrderFlowList,dbo.photoInfo " +
"where WorkOrderFlowList.WorkFlowListId in (" + inFlowsIDs + ") " +
"and WorkOrderFlowList.OrderProductId=photoInfo.OrderProductId";


                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {

                            num = Convert.ToInt32(dr.GetInt32(0));
                        }
                        else
                        {
                            num = 0;
                        }


                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return num;
        }
        /// <summary>
        /// 取得任务
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOrderId"></param>
        /// <returns></returns>
        public WorkOrderFlowList GetTaskByStatusAndOrderId(int inStatus, int inOrderId)
        {
            string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus + " AND it.orderInfo.OrderProductId = " + inOrderId;

            ObjectQuery<WorkOrderFlowList> flowLists =
                DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL).Include("orderInfo").Include("tb_Tech");
            if (flowLists.Count() > 0)
                return flowLists.First();
            else
            {

                return new WorkOrderFlowList();
            }


        }
        /// <summary>
        /// 取得任务
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOrderId"></param>
        /// <returns></returns>
        public WorkOrderFlowList GetTaskByWorkerFlowId(int inWorkerFlowId)
        {
            string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.WorkFlowListId=" + inWorkerFlowId;

            ObjectQuery<WorkOrderFlowList> flowLists =
                DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL).Include("orderInfo").Include("tb_Tech");
            if (flowLists.Count() > 0)
                return flowLists.First();
            else
            {

                return new WorkOrderFlowList();
            }


        }
        /// <summary>
        /// 更新数据
        /// </summary>
        public void Update()
        {
            DataEntites.SaveChanges();
        }
        /// <summary>
        /// 根据workerID 状态 时间 取得任务
        /// </summary>
        /// <param name="inWorkID"></param>
        /// <param name="inStatus"></param>
        /// <param name="inStartTime"></param>
        /// <param name="inEndTime"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public List<WorkOrderFlowList> GetMemberOrders(int inWorkID, int inStatus, DateTime inStartTime, DateTime inEndTime, int type)
        {
            string flowSql = "";
            switch (type)
            {
                case 0:
                    {
                        flowSql = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus +
                                  " AND it.AssignTime between cast('" + inStartTime + "' as System.DateTime) and cast('" +
                                  inEndTime + "' as System.DateTime) and it.UserId=" + inWorkID;
                    }
                    break;
                case 1:
                    {
                        flowSql = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus +
                                  " AND it.ReceiveTime between cast('" + inStartTime + "' as System.DateTime) and cast('" +
                                  inEndTime + "' as System.DateTime) and it.UserId=" + inWorkID;
                    }
                    break;
                case 2:
                    {
                        flowSql = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus +
                                 " AND it.CompletedTime between cast('" + inStartTime + "' as System.DateTime) and cast('" +
                                 inEndTime + "' as System.DateTime) and it.UserId=" + inWorkID;
                    }
                    break;
                default:
                    {
                        flowSql = "select value it from  WorkOrderFlowList as it WHERE it.STATUS=" + inStatus + " and it.UserId=" + inWorkID;

                    }
                    break;

            }
            ObjectQuery<WorkOrderFlowList> flowLists =
                DataEntites.CreateQuery<WorkOrderFlowList>(flowSql).Include("orderInfo").Include("tb_Tech");
            List<WorkOrderFlowList> lists = new List<WorkOrderFlowList>();
            foreach (WorkOrderFlowList list in flowLists)
            {
                lists.Add(list);
            }
            return lists;
        }

        /// <summary>
        /// 取得本组任务历史
        /// </summary>
        /// <param name="inProductId"></param>
        /// <param name="inOperates"></param>
        /// <returns></returns>
        public List<WorkOrderFlowList> GetFlowHistoryList(int inProductId, string inOperates)
        {
            //string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.orderInfo.OrderProductId=" + inProductId + " AND it.tb_Tech.TechId IN " + inOperates + " and it.Status<>" + (int)OrderFlowListStatus.Wait + "order by it.WorkOrder";
            string TaskSQL = "select value it from  WorkOrderFlowList as it WHERE it.orderInfo.OrderProductId=" + inProductId + " AND it.tb_Tech.TechId IN " + inOperates + "order by it.WorkOrder";

            ObjectQuery<WorkOrderFlowList> flowLists = DataEntites.CreateQuery<WorkOrderFlowList>(TaskSQL).Include("orderInfo").Include("tb_Tech");
            List<WorkOrderFlowList> rtnList = new List<WorkOrderFlowList>();
            foreach (var list in flowLists)
            {
                rtnList.Add(list);
            }
            return rtnList;
        }
        /// <summary>
        /// 取得当前任务节点
        /// </summary>
        /// <param name="inProductId"></param>
        /// <param name="inOperates"></param>
        /// <returns></returns>
        public string GetCurrTech(int inProductId, string inOperates)
        {
            string tecSql = "select value it  from WorkOrderFlowList as it where it.orderInfo.OrderProductId=" + inProductId + " AND it.tb_Tech.TechId IN " + inOperates + " and (it.status= " + (int)OrderFlowListStatus.Useable + "or it.status= " + (int)OrderFlowListStatus.UseableAccept + ")";
            ObjectQuery<WorkOrderFlowList> flowLists = DataEntites.CreateQuery<WorkOrderFlowList>(tecSql).Include("orderInfo").Include("tb_Tech");
            if (flowLists.Count() > 0)
            {
                return flowLists.First().tb_Tech.TechName;
            }
            else
            {
                return "";
            }
        }
        /// <summary>
        /// 取得到达本组时间
        /// </summary>
        /// <param name="inProductId"></param>
        /// <param name="inOperates"></param>
        /// <returns></returns>
        public DateTime GetArriveTime(int inProductId, string inOperates)
        {
            string minTechCurr =
                "select value Min(it.WorkOrder)  from WorkOrderFlowList as it where it.orderInfo.OrderProductId=" +
                inProductId + " AND it.tb_Tech.TechId IN " + inOperates;
            int minTech = DataEntites.CreateQuery<int>(minTechCurr).First();
            string maxTechPri =
                "select value cast(max(it.CompletedTime) as System.string) from  WorkOrderFlowList as it where it.orderInfo.OrderProductId=" +
                inProductId + " and it.WorkOrder<" + minTech;
            string arriveTime = DataEntites.CreateQuery<string>(maxTechPri).First();
            if (arriveTime == null)
            {
                return DateTime.Now;
            }
            else
            {
                return Convert.ToDateTime(arriveTime);
            }

        }
        /// <summary>
        /// 取得所有shop
        /// </summary>
        /// <returns></returns>
        public ObjectQuery<Shop> GetAllShopInfo()
        {
            return DataEntites.Shop.Where("True");

        }
        /// <summary>
        /// 取得总记录条数
        /// </summary>
        /// <param name="inCondition"></param>
        /// <param name="oper"></param>
        /// <returns></returns>
        public int[] GetPageCount(string inCondition, string oper, int inCountInPage)
        {
            string countSql = "select Tb_OrderBag.BagCode as BagCode from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 " +
               inCondition + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" + (int)OrderFlowListStatus.Complete;
            ObjectQuery<DbDataRecord> dr = DataEntites.CreateQuery<DbDataRecord>(countSql);
            int Recordcount = dr.Count();
            int rtnPage = 0;
            if (Recordcount % inCountInPage == 0)
            {
                rtnPage = Recordcount / inCountInPage;
            }
            else
            {
                rtnPage = (Recordcount / inCountInPage) + 1;
            }
            int[] rtnintarr = new int[2];
            rtnintarr[0] = Recordcount;
            rtnintarr[1] = rtnPage;
            return rtnintarr;
        }
        /// <summary>
        /// 取得记录
        /// </summary>
        /// <param name="inCondition"></param>
        /// <param name="inCurrPage"></param>
        /// <param name="oper"></param>
        /// <param name="inCountInPage"></param>
        /// <returns></returns>
        public List<RecordDDO> GetHistoryList(string inCondition, int inCurrPage, string oper, int inCountInPage)
        {
            int[] countArr = GetPageCount(inCondition, oper, inCountInPage);
            int left = countArr[0] - (inCurrPage - 1) * inCountInPage;
            int pagecount = 0;
            if (inCurrPage == countArr[1])
            {
                if (inCountInPage > left)
                {
                    pagecount = left;
                }
                else
                {
                    pagecount = inCountInPage;

                }
            }
            else
            {
                pagecount = inCountInPage;

            }
            string recordSql =
                            "select   Tb_OrderBag.BagCode as BagCode ,Tb_OrderBag.OrderBagId as OrderBagId,Tb_OrderBag.BagName as BagName ,Tb_OrderBag.OrderTime as OrderTime ,Tb_OrderBag.Shop.shopID as shopID ,Tb_OrderBag.Shop.ShopName as ShopName ,Tb_OrderBag.Priority as Priority ,orderInfo.OrderProductCode as OrderProductCode,orderInfo.OrderProductId as OrderProductId ,orderInfo.OrderProductName as OrderProductName,orderInfo.OrderPath as OrderPath,orderInfo.CtmName as CtmName,WorkOrderFlowList.UserId as UserId,WorkOrderFlowList.AssignTime as AssignTime,WorkOrderFlowList.CompletedTime as CompletedTime,WorkOrderFlowList.Status as Status,WorkOrderFlowList.tb_Tech.TechName as TechName,WorkOrderFlowList.tb_Tech.TechId,WorkOrderFlowList.ReceiveTime as ReceiveTime from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 " +
                inCondition + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" + (int)OrderFlowListStatus.Complete;
            //ObjectQuery<DbDataRecord> dr2 = DataEntites.CreateQuery<DbDataRecord>(recordSql);
            //ObjectQuery<DbDataRecord> dr = DataEntites.CreateQuery<DbDataRecord>(recordSql).Skip("it.OrderBagId", "0").Top("10");
            ObjectQuery<DbDataRecord> dr = DataEntites.CreateQuery<DbDataRecord>(recordSql).Skip("it.OrderProductId,it.TechId", (((inCurrPage - 1) * inCountInPage).ToString())).Top(pagecount.ToString());
            //int a = dr.Count();
            List<RecordDDO> rtnList = new List<RecordDDO>();
            foreach (DbDataRecord query in dr)
            {
                RecordDDO recordDdo = new RecordDDO();
                recordDdo.OrderBagId = Convert.ToInt32(query["OrderBagId"]);
                recordDdo.BagName = query["BagName"].ToString();
                recordDdo.BagCode = query["BagCode"].ToString();
                recordDdo.OrderPath = query["OrderPath"].ToString();
                recordDdo.OrderProductId = Convert.ToInt32(query["OrderProductId"]);
                recordDdo.CtmName = query["CtmName"].ToString();
                recordDdo.ShopName = query["ShopName"].ToString();
                recordDdo.UserName = GetUserById(Convert.ToInt32(query["UserId"])).UserName;//query["UserId"].ToString();

                recordDdo.OrderTime = Convert.ToDateTime(query["OrderTime"].ToString());
                recordDdo.OrderProductName = query["OrderProductName"].ToString();
                recordDdo.OrderProductCode = query["OrderProductCode"].ToString();
                recordDdo.TechName = query["TechName"].ToString();
                recordDdo.Priority = Convert.ToInt32(query["Priority"].ToString());
                recordDdo.AssignTime = Convert.ToDateTime(query["AssignTime"].ToString() == "" ? "2000-01-01" : query["AssignTime"].ToString());
                recordDdo.CompletedTime = Convert.ToDateTime(query["CompletedTime"].ToString() == "" ? "2000-01-01" : query["CompletedTime"].ToString());
                recordDdo.Status = "已完成";
                recordDdo.OrderTime = Convert.ToDateTime(query["OrderTime"].ToString() == "" ? "2000-01-01" : query["CompletedTime"].ToString());
                recordDdo.ReceiveTime = Convert.ToDateTime(query["ReceiveTime"].ToString() == "" ? "2000-01-01" : query["CompletedTime"].ToString());

                //recordDdo.CurrFlow = query["CurrFlow"].ToString();ReceiveTime
                //recordDdo.CurrFlow = query["CurrFlow"].ToString();
                rtnList.Add(recordDdo);

            }
            return rtnList;
        }
        /// <summary>
        /// 取得最大加急
        /// </summary>
        /// <param name="oper"></param>
        /// <returns></returns>
        public int MaxPriCurr(string oper)
        {
            //string countSql = "select value cast(max(Tb_OrderBag.Priority) as System.string) from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 "
            //   + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" + (int)OrderFlowListStatus.Useable + "and WorkOrderFlowList.UserId==0";
            //string maxPri = DataEntites.CreateQuery<string>(countSql).FirstOrDefault();


            //if (string.IsNullOrEmpty(maxPri))
            //{
            //    return -1;
            //}
            //else
            //{
            //    return Convert.ToInt32(maxPri);
            //}
            string operTemp = oper.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            Database db = null;
            int Priority = 0;
            try
            {
                db = DatabaseFactory.CreateDatabase();


                string strSQL = " select  max(orderInfo.Priority) from orderInfo,WorkOrderFlowList where orderInfo.OrderProductId = WorkOrderFlowList.OrderProductId  and  WorkOrderFlowList.TechId in (2,3,4) and WorkOrderFlowList.Status=1 and WorkOrderFlowList.UserId=0 and orderInfo.Status=1";


                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {

                            Priority = Convert.ToInt32(dr.GetInt32(0));
                        }
                        else
                        {
                            Priority = 0;
                        }


                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return Priority;


        }
        /// <summary>
        /// 取得最大等待加急
        /// </summary>
        /// <param name="oper"></param>
        /// <returns></returns>
        public int MaxPriWait(string oper)
        {

            string countSql = "select value cast(max(Tb_OrderBag.Priority) as System.string) from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 "
                + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" + (int)OrderFlowListStatus.Wait;
            string maxPri = DataEntites.CreateQuery<string>(countSql).FirstOrDefault();
            if (string.IsNullOrEmpty(maxPri))
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(maxPri);
            }

        }

        /// <summary>
        /// 取得未满组员列表
        /// </summary>
        /// <param name="inFullNum"></param>
        /// <param name="inGroupId"></param>
        /// <returns></returns>
        public List<users> GetOneUserUnFull(int inFullNum, int inGroupId, out List<int> outListTech)
        {
            List<users> userses = GetOnlineUsersByLeaderId(inGroupId);
            List<users> rtnListUsder = new List<users>();
            List<tb_Tech> listT = new List<tb_Tech>();
            List<int> listh = new List<int>();
            Hashtable hashTech = new Hashtable();
            foreach (users list in userses)
            {

                if (!CheckFull(inFullNum, list.userID))
                {
                    list.tb_Tech.Load();

                    listT = list.tb_Tech.ToList();
                    foreach (tb_Tech tech in listT)
                    {
                        if (!listh.Contains(tech.TechId))
                        {
                            listh.Add(tech.TechId);
                        }
                    }
                    rtnListUsder.Add(list);


                }
            }
            outListTech = listh;
            return rtnListUsder;
        }
        /// <summary>
        /// 取得组任务最少的组员并且任务未满
        /// </summary>
        /// <param name="inFullNum"></param>
        /// <param name="inGroupId"></param>
        /// <returns></returns>
        public int GetOneUserLeastTask(int inFullNum, int inGroupId)
        {
            List<users> userses = GetOnlineUsersByLeaderId(inGroupId);
            Hashtable intTable = new Hashtable();
            int min = 100;
            int rtnuserId = 0;
            foreach (users user in userses)
            {
                int intUseStatus = (int)OrderFlowListStatus.Useable;
                int orderProductStatus = (int)OrderInfoStatus.Doing;
                int count = DataEntites.WorkOrderFlowList.Where(p => p.UserId == user.userID && p.Status == intUseStatus && p.orderInfo.status == orderProductStatus).Count();
                if (count <= inFullNum && count < min)
                {
                    min = count;
                    rtnuserId = user.userID;
                }

            }
            return rtnuserId;
        }
        /// <summary>
        /// 取得组任务最少的组员并且任务未满
        /// </summary>
        /// <param name="inFullNum"></param>
        /// <param name="inGroupId"></param>
        /// <returns></returns>
        public int GetOneUserLeastTask(int inFullNum, int inGroupId,int inTechid)
        {
            List<users> userses = GetOnlineUsersByLeaderId(inGroupId);
            Hashtable intTable = new Hashtable();
            int min = 100;
            int rtnuserId = 0;
            foreach (users user in userses)
            {
                if (!user.tb_Tech.IsLoaded)
                {
                    user.tb_Tech.Load();
                }
                tb_Tech currtech = user.tb_Tech.FirstOrDefault(p=>p.TechId==inTechid);
                if (currtech == null)
                {
                    continue;
                }
                int intUseStatus = (int)OrderFlowListStatus.Useable;
                int orderProductStatus = (int)OrderInfoStatus.Doing;
                int count = DataEntites.WorkOrderFlowList.Where(p => p.UserId == user.userID && p.Status == intUseStatus && p.orderInfo.status == orderProductStatus).Count();
                if (count <= inFullNum && count < min)
                {
                    min = count;
                    rtnuserId = user.userID;
                }

            }
            return rtnuserId;
        }

        /// <summary>
        /// 检查组员是否完成任务
        /// </summary>
        /// <param name="inFullNum"></param>
        /// <param name="inUserId"></param>
        /// <returns></returns>
        private bool CheckFull(int inFullNum, int inUserId)
        {
            string checkSql = "select value it from WorkOrderFlowList as it where (it.Status=" + (int)OrderFlowListStatus.Useable + " or it.Status=" + (int)OrderFlowListStatus.UseableAccept + " or it.Status=" + (int)OrderFlowListStatus.Wait + ") and it.UserId=" + inUserId;
            int intNum = DataEntites.CreateQuery<int>(checkSql).Count();
            if (intNum >= inFullNum)
                return true;
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 取到当前最急任务
        /// </summary>
        /// <param name="inPri"></param>
        /// <returns></returns>
        public int GetOneWorkFlowCurr(string oper, out int outProductId)
        {

            //int maxPri = MaxPriCurr(oper);
            //string taskSql = "select  WorkOrderFlowList.WorkFlowListId as WorkFlowListId  from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 "
            //                 + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" +
            //                 (int)OrderFlowListStatus.Useable + "  and WorkOrderFlowList.UserId=0 and Tb_OrderBag.Priority=" + maxPri;
            //DbDataRecord record = DataEntites.CreateQuery<DbDataRecord>(taskSql).FirstOrDefault();
            //if (record == null)
            //{
            //    outProductId = 0;
            //    return 0;
            //}
            //int rtnId = Convert.ToInt32(record[0]);
            //GetTaskByWorkerFlowId(rtnId).orderInfoReference.Load();
            //outProductId = GetTaskByWorkerFlowId(rtnId).orderInfo.OrderProductId;
            //return rtnId;

            int maxPri = MaxPriCurr(oper);
            string operTemp = oper.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            Database db = null;
            int workFlowId = 0;
            try
            {
                db = DatabaseFactory.CreateDatabase();


               // string strSQL = " select min(WorkFlowListId) from WorkOrderFlowList,orderInfo where orderInfo.OrderProductId=WorkOrderFlowList.OrderProductId  and orderInfo.status=1 and WorkOrderFlowList.TechId in(2,3,4) and WorkOrderFlowList.UserId=0 and orderInfo.Priority= " + maxPri;
                string strSQL = " select min(WorkFlowListId) from WorkOrderFlowList,orderInfo where orderInfo.OrderProductId=WorkOrderFlowList.OrderProductId  and orderInfo.status=1 and WorkOrderFlowList.TechId in" + operTemp + " and WorkOrderFlowList.UserId=0 and orderInfo.Priority= " + maxPri;


                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {

                            workFlowId = Convert.ToInt32(dr.GetInt32(0));
                        }
                        else
                        {
                            workFlowId = 0;
                        }


                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            if (workFlowId == 0)
            {
                outProductId = 0;
                return 0;
            }
            GetTaskByWorkerFlowId(workFlowId).orderInfoReference.Load();
            outProductId = GetTaskByWorkerFlowId(workFlowId).orderInfo.OrderProductId;
            return workFlowId;

        }
        /// <summary>
        /// 取到当前最急任务
        /// </summary>
        /// <param name="inPri"></param>
        /// <returns></returns>
        public int GetWorkFlowCurrLiset(string oper)
        {

            int maxPri = MaxPriCurr(oper);
            string taskSql = "select  WorkOrderFlowList.WorkFlowListId as WorkFlowListId  from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 "
                             + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" +
                             (int)OrderFlowListStatus.Useable + "  and WorkOrderFlowList.UserId=0 and Tb_OrderBag.Priority=" + maxPri;
            DbDataRecord record = DataEntites.CreateQuery<DbDataRecord>(taskSql).FirstOrDefault();
            if (record == null)
            {

                return 0;
            }
            int rtnId = Convert.ToInt32(record[0]);
            GetTaskByWorkerFlowId(rtnId).orderInfoReference.Load();

            return rtnId;
        }
        /// <summary>
        /// 取到等待最急任务
        /// </summary>
        /// <param name="inPri"></param>
        /// <returns></returns>
        public int GetOneWorkFlowWait(string oper, out int outProductId)
        {
            int maxPri = MaxPriWait(oper);
            string taskSql = "select   WorkOrderFlowList.WorkFlowListId as WorkFlowListId  from ((orderInfo inner join Tb_OrderBag  on orderInfo.Tb_OrderBag.OrderBagId =Tb_OrderBag.OrderBagId )  inner join WorkOrderFlowList on WorkOrderFlowList.orderInfo.OrderProductId = orderInfo.OrderProductId) where 1=1 "
                             + "  and  WorkOrderFlowList.tb_Tech.TechId in " + oper + " and WorkOrderFlowList.Status=" +
                             (int)OrderFlowListStatus.Wait + " and Tb_OrderBag.Priority=" + maxPri + "and WorkOrderFlowList.UserId=0 ";
            DbDataRecord record = DataEntites.CreateQuery<DbDataRecord>(taskSql).FirstOrDefault();
            if (record == null)
            {
                outProductId = 0;
                return 0;
            }
            int rtnId = Convert.ToInt32(record[0]);

            GetTaskByWorkerFlowId(rtnId).orderInfoReference.Load();
            outProductId = GetTaskByWorkerFlowId(rtnId).orderInfo.OrderProductId;
            return rtnId;
        }
        /// <summary>
        /// 取得上次操作人
        /// </summary>
        /// <param name="inopId"></param>
        /// <param name="oper"></param>
        /// <returns></returns>
        public int GetUserOperLast(int inopId, string oper)
        {
            string userSql = "select value it from WorkOrderFlowList as it where it.orderInfo.OrderProductId =" + inopId + " and it.tb_Tech.TechId in " + oper + " order by it.WorkOrder desc";
            WorkOrderFlowList list = DataEntites.CreateQuery<WorkOrderFlowList>(userSql).FirstOrDefault();
            if (list == null)
            {
                return 0;
            }
            else
            {
                return list.UserId;
            }
        }
        /// <summary>
        /// 取得客户的所有产品
        /// </summary>
        /// <param name="inCtmName"></param>
        /// <returns></returns>
        public List<orderInfo> GetProductByCtmName(string inCtmName)
        {
            return DataEntites.orderInfo.Where(p => p.CtmName == inCtmName).ToList();
        }

        public List<WorkOrderFlowList> GetUnCompleteTaskByUserId(int inUserId)
        {
            string flowSql = "select value it from WorkOrderFlowList as it where (it.Status=" + (int)OrderFlowListStatus.Useable + " or it.Status=" + (int)OrderFlowListStatus.Wait + " ) and it.UserId=" + inUserId;
            ObjectQuery<WorkOrderFlowList> lists = DataEntites.CreateQuery<WorkOrderFlowList>(flowSql);
            List<WorkOrderFlowList> rtnList = new List<WorkOrderFlowList>();
            foreach (WorkOrderFlowList list in lists)
            {
                rtnList.Add(list);
            }
            return rtnList;
        }
        /// <summary>
        /// 查找24小时内做过该客户产品的组员名字在线
        /// </summary>
        /// <param name="inCtmName"></param>
        /// <returns></returns>
        //public int GetTaskUserIDIn24Hours(string inCtmName)
        //{

        //}
        /// <summary>
        /// 根据状态取得照片面积
        /// 
        /// </summary>
        /// <param name="oper"></param>
        /// <param name="status"> 0,未分配，1已分配</param>
        /// <returns></returns>
        public double GetAreasByStatus(string oper, int status)
        {
            Database db = null;
            double areas = 0;
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string strSQL = "";
                if (status == 0)
                {

                    strSQL = "  select sum(photoInfo.Height*photoInfo.Width/100/100*2.54*2.54)  as countNum    from WorkOrderFlowList,photoInfo,orderinfo where WorkOrderFlowList.techid in  " + oper + " and WorkOrderFlowList.UserId=0 and photoInfo.orderProductId=WorkOrderFlowList.OrderProductId and WorkOrderFlowList.Status=1 and orderinfo.orderProductId=WorkOrderFlowList.OrderProductId and orderinfo.Status=1";
                }
                else
                {
                    //strSQL = " select sum(areas) as countNum from ( select sum(Height*Width/100/100*2.54*2.54) as areas  from WorkOrderFlowList,photoInfo where WorkOrderFlowList.techid in " + oper + " and WorkOrderFlowList.UserId!=0 and photoInfo.orderProductId=WorkOrderFlowList.OrderProductId group by WorkOrderFlowList.OrderProductId ) as ta";
                    strSQL = " select sum(photoInfo.Height*photoInfo.Width/100/100*2.54*2.54) as countNum  from WorkOrderFlowList,photoInfo,orderinfo where WorkOrderFlowList.techid in " + oper + "  and WorkOrderFlowList.UserId!=0 and orderinfo.orderProductId=WorkOrderFlowList.OrderProductId and orderinfo.Status=1 and photoInfo.orderProductId=WorkOrderFlowList.OrderProductId and (WorkOrderFlowList.Status=2 or WorkOrderFlowList.Status=1)";

                }

                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {

                            areas = Convert.ToDouble(dr.GetDouble(0));
                        }
                        else
                        {
                            areas = 0.00;
                        }


                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return areas;



        }
        /// <summary>
        /// 取得任务列表
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOpe"></param>
        /// <returns></returns>
        public DataTable GetTaskDataTable(string assignStatus,string workflowName, string inOpe)
        {
            string operTemp = inOpe.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            string statusSql="";
            string whereSql = "where 1=1 ";
            if (operTemp.Contains("3"))
            {
                statusSql = " and (WorkOrderFlowList.Status=0 or WorkOrderFlowList.Status=1)";
            }
            else
            {
                statusSql = " and WorkOrderFlowList.Status =1 ";
            }
            if (assignStatus != "全部" && assignStatus != string.Empty)
            {
                whereSql += " and status = '" + assignStatus +"'";
            }
            if (workflowName != "全部" && workflowName != "当前"&&workflowName !=string.Empty)
            {
                whereSql += " and TechName = '" + workflowName + "'";
            }
            string strTech = " and (";
            string tempStr=operTemp.TrimStart('(').TrimEnd(')');
            foreach (string techid in tempStr.Split(','))
            {
                strTech += "WorkOrderFlowList.TechId=" + techid + " or ";

            }
            strTech = strTech.Substring(0,strTech.Length - 3);
            strTech += ")";
            Database db = null;
            DataTable table = new DataTable();
            try
            {
                db = DatabaseFactory.CreateDatabase();



                string strSQL = "select  OrderBagId,OrderProductId,BagCode,FetchTime ,                                                                    " +
                                  "BagName,OrderProductCode,OrderProductName,Priority,ShopName,CtmName,                                                   " +
                                  "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime, IsCurrTech,                                                    " +
                                  "WorkFlowListId,PreCompleteTime,status, UserName,photonum ,area ,orderpath,remark from (                                                  " +
                                  "                                                                                                                       " +
                                  "select Tb_OrderBag.OrderBagId,orderInfo.OrderProductId,BagCode, Tb_OrderBag.FetchTime as  FetchTime,                    " +
                                  "BagName,OrderProductCode,OrderProductName,orderInfo.Priority,shopName as ShopName,CtmName as CtmName,                  " +
                                  "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime,                                                               " +
                                  "WorkFlowListId,PreCompleteTime,WorkOrderFlowList.UserId,                                                               " +

                                  "                                                                                                                       " +
                                  "case WorkOrderFlowList.status when 1 then '是' else '否' end as IsCurrTech,                                            " +
                                  "                                                                                                                       " +
                                  "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )      " +
                                  "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )      " +
                                  "                              when 2 then '已接收'                                                                     " +
                                  "                              when 3 then '错误'                                                                       " +
                                  "                              when 4 then '已完成' end                                                                 " +
                                  "as status   ,order_photo.photoNum,order_photo.area,orderinfo.orderpath ,orderinfo.remark                                                                    " +
                                  "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                                " +
                                  ",(select orderinfo.orderproductid,sum(photoinfo.totalnum) photoNum,sum(photoinfo.height*photoinfo.width*2.54*2.54/10000) area     " +
                                  "  from orderinfo ,photoInfo                                                                                            " +
                                  "  where orderinfo.orderproductId=photoInfo.orderproductId  and  orderinfo.status=1                                                           " +
                                  "    group by  orderinfo.orderproductid) order_photo                                                                    " +
                                  "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                        " +
                                  "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                                    " +
                                  "and orderInfo.status=1                                                                                                 " +
                                  //"and WorkOrderFlowList.TechId in " + operTemp + "                                                                       " +
                                   strTech+
                                   statusSql +
                                  "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                                 " +
                                  "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                                    " +
                                  " and order_photo.orderproductid =orderInfo.orderproductid                                                              " +
                                  ") as ta left join dbo.users on ta.userID=users.userID                                                                  " +
                                   whereSql+
                                  " order by OrderTime desc ";

                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                dbCommand.CommandTimeout = 3000;
                DataSet set = db.ExecuteDataSet(dbCommand);
                table = set.Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }
            return table;
        }
        /// <summary>
        /// 取得快速查询的加急列表
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOpe"></param>
        /// <returns></returns>
        public List<string> GetTaskPrioritylist(string inStatus, string inOpe)
        {
            string operTemp = inOpe.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            Database db = null;
            List<string> PriorityList = new List<string>();
            DataTable table = new DataTable();
            try
            {
                db = DatabaseFactory.CreateDatabase();



                string strSQL = "select  distinct orderInfo.Priority                                       " +
                                "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech   " +
                                "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId           " +
                                "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                       " +
                                "and orderInfo.status=1                                                    " +
                                "and WorkOrderFlowList.TechId in " + operTemp +
                                "and WorkOrderFlowList.Status =1                                 " +
                                "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                    " +
                                "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                       ";


                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                DataSet set = db.ExecuteDataSet(dbCommand);
                table = set.Tables[0];
                int rowsNum = table.Rows.Count;
                for (int i = 0; i < rowsNum; i++)
                {
                    PriorityList.Add(Convert.ToString(table.Rows[i][0]));
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            return PriorityList;
        }

        /// <summary>
        /// 取得快速查询的状态
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOpe"></param>
        /// <returns></returns>
        public List<string> GetTaskStatuslist(string inStatus, string inOpe)
        {
            string operTemp = inOpe.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            Database db = null;
            List<string> StatusList = new List<string>();
            StatusList.Add("全部");
            DataTable table = new DataTable();
            try
            {
                db = DatabaseFactory.CreateDatabase();



                string strSQL = "select distinct                                                                                                  " +
                                "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                "                              when 2 then '已接收'                                                               " +
                                "                              when 3 then '错误'                                                                 " +
                                "                              when 4 then '已完成' end                                                           " +
                                "as status                                                                                                        " +
                                "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                          " +
                                "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                  " +
                                "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                              " +
                                "and orderInfo.status=1                                                                                           " +
                                 "and WorkOrderFlowList.TechId in " + operTemp +
                                "and WorkOrderFlowList.Status  =1                                                                        " +
                                "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                           " +
                                "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                              ";



                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                DataSet set = db.ExecuteDataSet(dbCommand);
                table = set.Tables[0];
                int rowsNum = table.Rows.Count;
                for (int i = 0; i < rowsNum; i++)
                {
                    StatusList.Add(Convert.ToString(table.Rows[i][0]));
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            return StatusList;
        }

        /// <summary>
        /// 取得快速查询的jiedian
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOpe"></param>
        /// <returns></returns>
        public List<string> GetTaskTechlist(string inStatus, string inOpe)
        {
            string operTemp = inOpe.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            Database db = null;
            List<string> TechList = new List<string>();
            TechList.Add("全部");
            TechList.Add("当前");
            DataTable table = new DataTable();
            try
            {
                db = DatabaseFactory.CreateDatabase();



                string strSQL = "select distinct TechName                                                  " +
                                "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech   " +
                                "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId           " +
                                "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                       " +
                                "and orderInfo.status=1                                                    " +
                                 "and WorkOrderFlowList.TechId in " + operTemp +
                                "and WorkOrderFlowList.Status =1                                 " +
                                "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                    " +
                                "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                       ";




                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                DataSet set = db.ExecuteDataSet(dbCommand);
                table = set.Tables[0];
                int rowsNum = table.Rows.Count;
                for (int i = 0; i < rowsNum; i++)
                {
                    TechList.Add(Convert.ToString(table.Rows[i][0]));
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            return TechList;
        }

        /// <summary>
        /// 获取组员任务的照片总量                  
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="UseableStatus"></param>
        /// <param name="acceptStatus"></param>
        /// <returns></returns>
        public double GetUserPhotoNum(int userId)
        {
            Database db = null;
            double Num = 0;
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string strSQL = "select sum(photoInfo.Height*photoInfo.Width*2.54*2.54/10000) as Num from dbo.orderInfo,dbo.photoInfo ,dbo.WorkOrderFlowList " +
                                "where WorkOrderFlowList.OrderProductId=photoInfo.OrderProductId                " +
                                "and orderInfo.OrderProductId=photoInfo.OrderProductId                          " +
                                "and WorkOrderFlowList.Status in (1,2) and WorkOrderFlowList.UserId=" + userId +
                                "and orderInfo.status=1";




                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {

                            Num = Convert.ToDouble((dr.GetDouble(0)));
                        }
                        else
                        {
                            Num = 0;
                        }


                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return Num;

        }

        /// <summary>
        /// 获取组员任务的照片总量                  
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="UseableStatus"></param>
        /// <param name="acceptStatus"></param>
        /// <returns></returns>
        public int GetUserPhotoNumAll(int userId)
        {
            Database db = null;
            int Num = 0;
            try
            {
                db = DatabaseFactory.CreateDatabase();
                string strSQL = "select Count(*) as Num from dbo.orderInfo,dbo.photoInfo ,dbo.WorkOrderFlowList " +
                                "where WorkOrderFlowList.OrderProductId=photoInfo.OrderProductId                " +
                                "and orderInfo.OrderProductId=photoInfo.OrderProductId                          " +
                                "and WorkOrderFlowList.Status in (0,1,2) and WorkOrderFlowList.UserId=" + userId +
                                "and orderInfo.status=1";




                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);

                using (IDataReader dr = db.ExecuteReader(dbCommand))
                {
                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {

                            Num = Convert.ToInt32(dr.GetInt32(0));
                        }
                        else
                        {
                            Num = 0;
                        }


                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return Num;

        }
        /// <summary>
        /// 获取组员任务
        /// </summary>
        /// <param name="inUserID"></param>
        /// <param name="inStatus"></param>
        /// <returns></returns>
        public DataTable GetUserCurrTask(int inUserID)
        {

            Database db = null;


            DataTable table = new DataTable();
            try
            {
                db = DatabaseFactory.CreateDatabase();



                string strSQL = "select Tb_OrderBag.OrderBagId,orderInfo.OrderProductId,BagCode,                                                  " +
                                "BagName,OrderProductCode,OrderProductName,orderInfo.Priority,shopName as ShopName,CtmName as CtmName,            " +
                                "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime,                                                         " +
                                "WorkFlowListId,PreCompleteTime,WorkOrderFlowList.UserId,                                                         " +
                                "                                                                                                                 " +
                                "                                                                                                                 " +
                                "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                "                              when 2 then '已接收'                                                               " +
                                "                              when 3 then '错误'                                                                 " +
                                "                              when 4 then '已完成' end                                                           " +
                                "as status                                                                                                        " +
                                "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                          " +
                                "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                  " +
                                "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                              " +
                                "and orderInfo.status=1                                                                                           " +
                                "and WorkOrderFlowList.Status in (0,1,2,3)                                                                        " +
                                "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                           " +
                                "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                              " +
                                "and WorkOrderFlowList.UserId=" + inUserID;


                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                DataSet set = db.ExecuteDataSet(dbCommand);
                table = set.Tables[0];


            }
            catch (Exception ex)
            {
                throw;
            }
            return table;
        }
        /// <summary>
        /// 取得快速查询的商户列表
        /// </summary>
        /// <param name="inStatus"></param>
        /// <param name="inOpe"></param>
        /// <returns></returns>
        public List<string> GetTaskTechlist1(string inStatus, string inOpe)
        {
            string operTemp = inOpe.Replace('{', '(');
            operTemp = operTemp.Replace('}', ')');
            Database db = null;
            List<string> TechList = new List<string>();
            TechList.Add("全部");
            TechList.Add("当前");
            DataTable table = new DataTable();
            try
            {
                db = DatabaseFactory.CreateDatabase();



                string strSQL = "select distinct TechName                                                  " +
                                "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech   " +
                                "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId           " +
                                "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                       " +
                                "and orderInfo.status=1                                                    " +
                                 "and WorkOrderFlowList.TechId in " + operTemp +
                                "and WorkOrderFlowList.Status in (0,1,2,3)                                 " +
                                "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                    " +
                                "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                       ";




                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                DataSet set = db.ExecuteDataSet(dbCommand);
                table = set.Tables[0];
                int rowsNum = table.Rows.Count;
                for (int i = 0; i < rowsNum; i++)
                {
                    TechList.Add(Convert.ToString(table.Rows[i][0]));
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            return TechList;
        }
        /// <summary>
        /// 根据workerID 状态 时间 取得任务
        /// </summary>
        /// <param name="inWorkID"></param>
        /// <param name="inStatus"></param>
        /// <param name="inStartTime"></param>
        /// <param name="inEndTime"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable GetMemberOrdersNew(int inWorkID, DateTime inStartTime, DateTime inEndTime, int type)
        {
            Database db = null;

            DataTable table = new DataTable();
            db = DatabaseFactory.CreateDatabase();
            string flowSql = "";
            switch (type)
            {
                case 0:
                    {
                        flowSql = "select Tb_OrderBag.OrderBagId,orderInfo.OrderProductId,BagCode,                                                  " +
                                  "BagName,OrderProductCode,OrderProductName,orderInfo.Priority,shopName as ShopName,CtmName as CtmName,            " +
                                  "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime,                                                         " +
                                  "WorkFlowListId,PreCompleteTime,WorkOrderFlowList.UserId,                                                         " +
                                  "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                  "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                  "                              when 2 then '已接收'                                                               " +
                                  "                              when 3 then '错误'                                                                 " +
                                  "                              when 4 then '已完成' end                                                           " +
                                  "as status                                                                                                        " +
                                  "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                          " +
                                  "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                  " +
                                  "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                              " +
                                  "and orderInfo.status=1                                                                                           " +
                                  "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                           " +
                                  "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                              " +
                                  "and WorkOrderFlowList.UserId=" + inWorkID +
                                  "and WorkOrderFlowList.AssignTime between convert(datetime,'" + inStartTime + "') and convert(datetime,'" + inEndTime + "')       " +
                                  "and WorkOrderFlowList.Status<>4                                                                                  ";
                    }
                    break;
                case 1:
                    {
                        flowSql = "select Tb_OrderBag.OrderBagId,orderInfo.OrderProductId,BagCode,                                                  " +
                                    "BagName,OrderProductCode,OrderProductName,orderInfo.Priority,shopName as ShopName,CtmName as CtmName,            " +
                                    "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime,                                                         " +
                                    "WorkFlowListId,PreCompleteTime,WorkOrderFlowList.UserId,                                                         " +
                                    "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                    "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                    "                              when 2 then '已接收'                                                               " +
                                    "                              when 3 then '错误'                                                                 " +
                                    "                              when 4 then '已完成' end                                                           " +
                                    "as status                                                                                                        " +
                                    "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                          " +
                                    "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                  " +
                                    "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                              " +
                                    "and orderInfo.status=1                                                                                           " +
                                    "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                           " +
                                    "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                              " +
                                    "and WorkOrderFlowList.UserId=" + inWorkID +
                                    "and WorkOrderFlowList.ReceiveTime between convert(datetime,'" + inStartTime + "') and convert(datetime,'" + inEndTime + "')       " +
                                    "and WorkOrderFlowList.Status<>4                           ";
                    }
                    break;
                case 2:
                    {
                        flowSql = "select Tb_OrderBag.OrderBagId,orderInfo.OrderProductId,BagCode,                                                  " +
                                      "BagName,OrderProductCode,OrderProductName,orderInfo.Priority,shopName as ShopName,CtmName as CtmName,            " +
                                      "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime,                                                         " +
                                      "WorkFlowListId,PreCompleteTime,WorkOrderFlowList.UserId,                                                         " +
                                      "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                      "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                      "                              when 2 then '已接收'                                                               " +
                                      "                              when 3 then '错误'                                                                 " +
                                      "                              when 4 then '已完成' end                                                           " +
                                      "as status                                                                                                        " +
                                      "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                          " +
                                      "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                  " +
                                      "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                              " +
                                      "and orderInfo.status=1                                                                                           " +
                                      "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                           " +
                                      "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                              " +
                                      "and WorkOrderFlowList.UserId=" + inWorkID +
                                      "and WorkOrderFlowList.CompletedTime between convert(datetime,'" + inStartTime + "') and convert(datetime,'" + inEndTime + "')       " +
                                      "and WorkOrderFlowList.Status<>4                           ";
                    }
                    break;
                default:
                    {
                        flowSql = "select Tb_OrderBag.OrderBagId,orderInfo.OrderProductId,BagCode,                                                  " +
                                         "BagName,OrderProductCode,OrderProductName,orderInfo.Priority,shopName as ShopName,CtmName as CtmName,            " +
                                         "TechName,OrderTime,AssignTime,ReceiveTime,CompletedTime,                                                         " +
                                         "WorkFlowListId,PreCompleteTime,WorkOrderFlowList.UserId,                                                         " +
                                         "case WorkOrderFlowList.status when 0 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                         "                              when 1 then (case WorkOrderFlowList.UserId when 0 then '未分配' else '已分配' end )" +
                                         "                              when 2 then '已接收'                                                               " +
                                         "                              when 3 then '错误'                                                                 " +
                                         "                              when 4 then '已完成' end                                                           " +
                                         "as status                                                                                                        " +
                                         "from dbo.WorkOrderFlowList,dbo.orderInfo, dbo.Tb_OrderBag ,Shop,tb_Tech                                          " +
                                         "where WorkOrderFlowList.OrderProductId=orderInfo.OrderProductId                                                  " +
                                         "and orderInfo.OrderBagId=dbo.Tb_OrderBag.OrderBagId                                                              " +
                                         "and orderInfo.status=1                                                                                           " +
                                         "and dbo.Shop.ShopId=Tb_OrderBag.shopID                                                                           " +
                                         "and dbo.WorkOrderFlowList.TechId=dbo.tb_Tech.TechId                                                              " +
                                         "and WorkOrderFlowList.UserId=" + inWorkID +
                                         "and WorkOrderFlowList.Status<>4 ";

                    }
                    break;

            }

            DbCommand dbCommand = db.GetSqlStringCommand(flowSql);
            DataSet set = db.ExecuteDataSet(dbCommand);
            table = set.Tables[0];
            return table;
        }
       public  List<int> GetUserTechidsByUserId(int inUserid)
       {
            List<int> rtnInt=new List<int>();
           using (newSampleYPDBEntities db=new newSampleYPDBEntities())
           {
               users user = db.users.FirstOrDefault(p => p.userID == inUserid);
               user.tb_Tech.Load();
               List<tb_Tech> listTech = user.tb_Tech.ToList();
               foreach (tb_Tech tech in listTech)
               {
                   rtnInt.Add(tech.TechId);
               }
           }
           return rtnInt;
       }

       public DataTable GetPerfromances(DateTime startDT, DateTime endDT, List<int> selectedUserIDList)
       {
           string users = "";
           selectedUserIDList.ForEach(delegate(int user)
           {
               users += user + ",";
           });
           users = users.TrimEnd(',');
           string SQL = "select  OrderproductId, TechName, ShopName, CtmName, OrderProductName" +    //,   CASE CHARINDEX('_',ORDERPRODUCTNAME) WHEN 0 THEN ORDERPRODUCTNAME ELSE "+
               //           "(case OrderProductName when ' ' then ' ' else REVERSE(LEFT(REVERSE(New_EmployeePerformance.OrderProductName), CHARINDEX('_', REVERSE(New_EmployeePerformance.OrderProductName)) - 1)) end) END  AS ProductName" +
                        ",CASE CHARINDEX('_',ORDERPRODUCTNAME) WHEN 0 THEN ORDERPRODUCTNAME ELSE (case OrderProductName when ' ' then ' ' else REVERSE(LEFT(REVERSE(New_EmployeePerformance.OrderProductName), CHARINDEX('_', REVERSE(New_EmployeePerformance.OrderProductName)) - 1)) end) END  AS ProductName,OrderCreateTime,ReceivedTime, UserName,case Status when 7 then '暂停' when 12 then '已删除'  when 8 then '已完成 ' else  '正常生产' end  as StatusName , PhotoTotalNum, SinglePageNum, " +
                        " MultiplePageNum, OrderPath, TechCompletedTime, OrderConpletedTime, PreOrderAreas, OrderArea, OrderCount, ProductPhotoNum " +
                        " from New_EmployeePerformance,users,tb_Tech " +
                        " where New_EmployeePerformance.TechId=tb_Tech.TechId and users.userid=New_EmployeePerformance.userid " +
                        " and New_EmployeePerformance.userid in (" + users + ") and TechCompletedTime<'" + endDT.ToString() + "'and  TechCompletedTime>'" + startDT.ToString() + "'";
           return CommOperDB.ExceSQLDataTable(SQL);
       }
    }
}
